from django.contrib import admin

from PeopleInfo.models import Gxxs0101 as PeopleInfoGxxs0101, Contacts
from reversion.admin import VersionAdmin
from django.contrib import messages
from django.http import HttpResponseRedirect
from django.db import transaction, connections
from DataCenter.resources import *
from DataCenter.model import *
from django.utils.html import mark_safe
import random
import string
import datetime
from gxjg.models import Gxjg0101
from gxxs.models import Gxxs0101, Gxxs0701
import logging



# Register your models here.

@admin.register(Others)
class OthersAdmin(admin.ModelAdmin):
    list_display = ['xgh', 'xm', 'xb', 'bjmc', 'ssjg', 'glbm', 'yddh', 'remark', 'cjsj']
    list_filter = ['sfzjlxm', 'glbm']
    search_fields = ['xgh', 'xm', 'sfzjh']
    ordering = ('-cjsj', )

    def move_to_teacher(self, request, queryset):
        for item in queryset:
            data = {
                'gh': item.xgh,
                'xm': item.xm,
                'xbm': item.xb,
                'sfzjlxm': item.sfzjlxm,
                'sfzjh': item.sfzjh,
                'dwh': item.glbm,
                'type': 1,
                'remarks': item.remark
            }
            if item.sfzjlxm == '1' and item.sfzjlxm:
                data['csdm'] = item.sfzjh[0:6]
                data['csrq'] = item.sfzjh[6:14]

            with transaction.atomic():

                # 添加数据中心教师
                Gxjg0101.objects.create(**data)
                # 插入通讯方式表
                '''
                if item.yddh:
                    data1 = {
                        'xgh': item.xgh,
                        'yddh': item.yddh
                    }
                    Contacts.objects.create(**data1)
                '''
                # 删除校外人员数据
                Others.objects.filter(xgh=item.xgh).delete()

        # 修改数据后重定向url到新加数据页面
        return HttpResponseRedirect('/admin/gxjg/gxjg0101/')
    def move_to_student(self, request, queryset):
        for item in queryset:
            # 需要插入两个表 一个是gxxs_temp.gxxs0101 和 gxxs_temp.gxxs0701
            data1 = {
                'xh': item.xgh,
                'xm': item.xm,
                'xbm': item.xb,
                'sfzjlxm': item.sfzjlxm,
                'sfzjh': item.sfzjh,
                'source': '校外人员表移动',
                'remarks': item.remark
            }

            if item.sfzjlxm == '1' and item.sfzjlxm:
                data1['csdm'] = item.sfzjh[0:6]
                data1['csrq'] = datetime.datetime.strptime(item.sfzjh[6:14], "%Y%m%d").strftime("%Y-%m-%d 00:00:00")

            data2 = {
                'xh': item.xgh,
                'dwh': item.glbm,
                'szbh': item.bjbh,
                'zym': item.zybh,
                'sznj': item.bjbh[0:4],
            }

            with transaction.atomic():
                # 插入基本信息表
                Gxxs0101.objects.create(**data1)
                try:
                    # 插入学生学籍表
                    Gxxs0701.objects.create(**data2)
                except Exception as e:
                    logging.error(data2['xh'] + "插入学籍表报错")
                    pass
                    

                # 删除校外人员数据
                Others.objects.filter(xgh=item.xgh).delete()
        return HttpResponseRedirect('/admin/gxxs/gxxs0101')

    move_to_student.short_description = '移动所选的 校外人员到学生表'
    move_to_teacher.short_description = '移动所选的 校外人员到教师表'

    actions = [move_to_teacher, move_to_student, ]


@admin.register(SoftwareCompany)
class SoftwareCompanyAdmin(VersionAdmin):
    list_display = ['id', 'name', 'address', 'tel', 'contact', 'update_time']
    list_filter = ['create_time', 'update_time']
    search_fields = ['name', 'address', 'tel', 'contact']


@admin.register(System)
class SystemAdmin(VersionAdmin):
    list_display = ['id', 'alias', 'type', 'department', 'contact_person', 'is_offer_data_interface', 'is_support_write_back', 'remarks', 'update_time']
    list_filter = ['department', 'type', 'is_use_cas', 'is_offer_data_interface', 'is_support_write_back', 'create_time', 'update_time']
    search_fields = ['title', 'alias', 'contact_person', 'remarks']

    filter_horizontal = []


@admin.register(ViewShareRecord)
class ViewShareRecordAdmin(VersionAdmin):
    list_display = ['title', 'show_system_title', 'department', 'contact_person', 'name', 'account', "password", 'enable', 'create_by', 'create_time']
    list_filter = ['system', 'department', 'purpose', 'enable', 'create_by', 'create_time']
    search_fields = ['system', 'contact_person', 'name', 'phone', 'account']
    ordering = ['-update_time']

    filter_horizontal = ['system']
    # filter_vertical = ['system']

    fieldsets = (
        ('基本信息', {"fields": ["title", "system", 'department', 'contact_person', 'purpose', "name", "phone", "requirement"]}),
        ("视图信息", {"fields": ["database", "hostname", "port", "account", "password", "host"]}),
        ("其他信息", {"fields": ["enable", 'create_by', 'remarks', 'create_time', 'update_time']}),
    )

    readonly_fields = ['password', 'create_by', 'create_time', 'update_time']
    save_on_top = True

    def show_system_title(self, obj):
        system_titles = [item.title for item in obj.system.all()]
        return system_titles if system_titles else None

    show_system_title.short_description = '系统'

    @staticmethod
    def generate_password(password_length: int):
        characters = []
        for i in range(password_length):
            characters.append(random.choice(string.ascii_letters + string.digits + string.punctuation))
        return ''.join(characters)

    @staticmethod
    def create_database(account, host, password):
        connection = connections['data_center_by_root']
        with connection.cursor() as cursor:
            create_database_sql = "create database {database} character set utf8mb4;"
            cursor.execute(create_database_sql.format(database=account))
            cursor.execute("use mysql;")
            create_user_sql = "GRANT Execute, Select, Show View ON `{database}`.* TO `{account}`@`{host}` IDENTIFIED BY '{password}';"
            cursor.execute(create_user_sql.format(database=account, account=account, host=host, password=password))

    @staticmethod
    def lock_user(account, host):
        connection = connections['data_center_by_root']
        with connection.cursor() as cursor:
            sql = "ALTER USER '{account}'@'{host}' ACCOUNT LOCK;"
            cursor.execute(sql.format(account=account, host=host))

    @staticmethod
    def unlock_user(account, host):
        connection = connections['data_center_by_root']
        with connection.cursor() as cursor:
            sql = "ALTER USER '{account}'@'{host}' ACCOUNT UNLOCK;"
            cursor.execute(sql.format(account=account, host=host))

    @transaction.atomic
    def save_model(self, request, obj, form, change):
        if not change:
            obj.password = ViewShareRecordAdmin.generate_password(16)
            obj.host = obj.host if obj.host else '%'
            nickname = request.user.last_name + request.user.first_name
            obj.create_by = nickname if nickname else request.user.username

            # 创建相关数据库和用户
            try:
                ViewShareRecordAdmin.create_database(obj.account, obj.host, obj.password)
            except Exception as e:
                logging.error(e)
                self.message_user(request, '创建数据库或用户失败', level=messages.ERROR)

        if obj.enable:
            ViewShareRecordAdmin.unlock_user(obj.account, obj.host)
        else:
            ViewShareRecordAdmin.lock_user(obj.account, obj.host)
        super(ViewShareRecordAdmin, self).save_model(request, obj, form, change)

    def get_readonly_fields(self, request, obj=None):
        if obj:
            return self.readonly_fields + ['account', 'host']

        return self.readonly_fields


class FieldMetaInline(admin.StackedInline):
    model = FieldMetaInfo
    extra = 1


@admin.register(DataDictionaryDirectory)
class DataDictionaryDirectoryAdmin(admin.ModelAdmin):
    list_display = ['schema_comment', 'table_comment', 'column_comment', 'column_type', 'department', 'system', 'maintainer', 'update_time']
    list_filter = ['table_schema', 'schema_comment', 'table_name', 'table_comment', 'column_type', 'is_nullable', 'field_meta__department', 'field_meta__system', 'field_meta__maintainer']
    search_fields = ['table_comment', 'column_comment', 'field_meta__department', 'field_meta__system', 'field_meta__maintainer']

    readonly_fields = ['id', 'table_schema', 'schema_comment', 'table_name', 'table_comment', 'column_name', 'column_comment', 'column_type', 'is_nullable', 'column_default', 'extra']

    inlines = [FieldMetaInline]

    fieldsets = (
        ('字段信息', {"fields": [
            "id",
            ('table_schema', 'table_name', 'column_name'),
            ('schema_comment', 'table_comment', 'column_comment'),
            'column_type',
            'is_nullable',
            'column_default',
            'extra']
        }),
    )

    def save_model(self, request, obj, form, change):
        pass

    def department(self, obj):
        return obj.field_meta.department
    department.short_description = '管理部门'

    def system(self, obj):
        return obj.field_meta.system
    system.short_description = '来源系统'

    def maintainer(self, obj):
        return obj.field_meta.maintainer
    maintainer.short_description = '维护员'

    def update_time(self, obj):
        return obj.field_meta.update_time
    update_time.short_description = '更新时间'



